#delim ;
set more off;
capture log close;
capture clear;
*log using , text replace;
set memory 200m;

/***!***!***!***!***!*** [National_banks_crisis1.do ] ***!***!***!***!***!
*
* Project: National Banks  		
* Programmer:  Scott Fulford
*
* Date:    	 8/1/2010
*
* Auditor:      
* Audit Date:   
*
* Purpose:      
* 1) Combine cities with states
* 2) Create com
* 3)
* 		      
*
* Ouputs: 
*		
*
*
***!***!***!***!***!***!***!***!***!***!***!***!***!***!***!***!***/


/***Define Global Directory ****/
	//Change rootdir to where store files
	global ROOTDIR "F:/Dropbox/national banks peg/Replication_files";
	local PROGDIR  "$ROOTDIR";
	local INDIR  "$ROOTDIR/data";
	local OUTDIR  "$ROOTDIR/data";
	local GRAPHDIR "$ROOTDIR";
/*******************************/

cd "`INDIR'";

use National_bank_call_reports, clear;
format %tdMon_DD,_CCYY call_date;

/*Combine cities with states except for New York City

Brooklyn in NYC (or New York)?

*/

foreach pair in 
`""           CITY OF ALBANY" "NEW YORK            ""'
`""        CITY OF BALTIMORE" "MARYLAND            ""'
`""           CITY OF BOSTON" "MASSACHUSETTS       ""'
`""         CITY OF BROOKLYN" "NEW YORK            ""'
/*
`""          CITY OF CHICAGO" "ILLINOIS            ""'
*/
`""       CITY OF CINCINNATI" "OHIO                ""'
`""        CITY OF CLEVELAND" "OHIO                ""'
`""         CITY OF COLUMBUS" "OHIO                ""'
`""           CITY OF DENVER" "COLORADO            ""'
`""       CITY OF DES MOINES" "IOWA                ""'
`""          CITY OF DETROIT" "MICHIGAN            ""'
`""          CITY OF HOUSTON" "TEXAS               ""'
`""     CITY OF INDIANAPOLIS" "INDIANA             ""'
`""          CITY OF LINCOLN" "NEBRASKA            ""'
`""      CITY OF LOS ANGELES" "CALIFORNIA          ""'
`""       CITY OF LOUISVILLE" "KENTUCKY            ""'
`""        CITY OF MILWAUKEE" "WISCONSIN           ""'
`""      CITY OF MINNEAPOLIS" "MINNESOTA           ""'
`""      CITY OF NEW ORLEANS" "LOUISIANA           ""'
/*Combine New York City with New York for state level analysis
`""         CITY OF NEW YORK" "NEW YORK            ""'
*/
`""            CITY OF OMAHA" "NEBRASKA            ""'
`""     CITY OF PHILADELPHIA" "PENNSYLVANIA        ""'
`""       CITY OF PITTSBURGH" "PENNSYLVANIA        ""'
`""         CITY OF PORTLAND" "OREGON              ""'
`""    CITY OF SAN FRANCISCO" "CALIFORNIA          ""'
`""         CITY OF SAVANNAH" "GEORGIA             ""'
`""        CITY OF ST JOSEPH" "MISSOURI            ""'
/*
`""         CITY OF ST LOUIS" "MISSOURI            ""'
*/
`""          CITY OF ST PAUL" "MINNESOTA           ""'
`""       CITY OF WASHINGTON" "DISTRICT OF COLUMBIA""'
`""              KANSAS CITY" "MISSOURI            ""'
/*Dakota splits and becomes North and South, combine*/
`""             NORTH DAKOTA" "DAKOTA              ""'
`""             SOUTH DAKOTA" "DAKOTA              ""'
`""      CITY OF CEDAR RAPIDS"  "IOWA              ""'
`""            CITY OF DALLAS"  "TEXAS             ""'
`""           CITY OF DUBUQUE"  "IOWA           ""'
`""        CITY OF FORT WORTH"  "TEXAS             ""'
`""         CITY OF GALVESTON"  "TEXAS             ""'
`""CITY OF KANSAS CITY KANSAS"  "KANSAS            ""'
`""            CITY OF PUEBLO"  "COLORADO          ""'
`""    CITY OF SALT LAKE CITY"  "UTAH              ""'
`""       CITY OF SAN ANTONIO"  "TEXAS             ""'
`""           CITY OF SEATTLE"  "WASHINGTON        ""'
`""              CITY OF WACO"  "TEXAS             ""'
`""           CITY OF WICHITA"  "KANSAS            ""'
`""      CITY OF KANSAS CITYK"  "KANSAS""'
`""      CITY OF KANSAS CITYM"  "MISSOURI""'
`""          CITY OF MUSKOGEE"  "OKLAHOMA""'
`""     CITY OF OKLAHOMA CITY"  "OKLAHOMA""'
`""       CITY OF SOUTH OMAHA"  "NEBRASKA""'
`""           CITY OF SPOKANE"  "WASHINGTON""'
`""            CITY OF TACOMA"  "WASHINGTON""'
`""            CITY OF TOPEKA"  "KANSAS""'

{;
		gettoken city pair: pair;
		gettoken state pair: pair;
		local city = trim("`city'");
		local state = trim("`state'");
		replace location = "`state'" if location =="`city'";
};
/*Collapse cities into states*/
sort call_date location;


 /*Preserve labels*/
 foreach v of var * {;
 local l`v' : variable label `v';
        if `"`l`v''"' == "" {;
         local l`v' "`v'";
      };
  };
ds call_year call_date call_date_order location callnum, not;
collapse (sum) `r(varlist)' (mean)  call_year  call_date_order callnum, by(call_date location);

 foreach v of var * {;
     label var `v' "`l`v''";
  };
capture drop loc_no;
sort location;
encode location, gen(loc_no);
xtset loc_no call_date_order; /*For regressions xtset by call date number*/

cd "`OUTDIR'";
saveold National_bank_call_reports_states, replace;
exit;
